﻿/*-----------------------------------------------------------------------
 * <copyright file="DataProvider.cs" company="O-RID">
 *     Copyright (c)2010 O-RID Co.,Ltd.
 * </copyright>
 * <author>Zhang Jun</author>
 * <summary>
 *          所有无法归类的查询 服务
 *          StatisticsToolsDB_new 数据库
 *          WCFTheOthersQuery.svc服务实现的具体方法
 *          Get（Set）+服务方法名
 *
 *          作成日： 07/27/2010
 *                  11/08/2010 增加 基礎データ抽取
 *                  11/10/2010 增加 オーダ情報 抽取
 *                  12/24/2010 增加 F和G 对应权限字段
 *                  06/29/2011 增加 ParintID查询信息
 * </summary>
 *-----------------------------------------------------------------------
 */

namespace Orid.Management.Statistics.Service.OPUnitService.TheOthersQuery
{
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using Orid.Management.Statistics.Service.OPUnitService.TheOthersQuery.ServiceModel;

    public class DataProvider
    {
        #region  OP权限查询
        /// <summary>
        /// Selects the operator roles query infor.
        /// </summary>
        /// <param name="whereParameter">The where parameter.</param>
        /// <returns></returns>
        public static List<OperatorRolesData> GetSelectOperatorRolesQueryInfor(List<WhereParameters> whereParameter) 
        {
            List<OperatorRolesData> OperatorRolesInforList = new List<OperatorRolesData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
               
                using (SqlCommand cmd = new SqlCommand("OperatorRolesQuery", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@Server", SqlDbType.NVarChar));
                    cmd.Parameters["@Server"].Value = whereParameter[0].WhereServer;

                    cmd.Parameters.Add(new SqlParameter("@OpID", SqlDbType.NVarChar));
                    cmd.Parameters["@OpID"].Value = whereParameter[0].WhereOpID;

                    cmd.Parameters.Add(new SqlParameter("@Unit", SqlDbType.NVarChar));
                    cmd.Parameters["@Unit"].Value = whereParameter[0].WhereUnit;

                    cmd.CommandTimeout = 380;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        OperatorRolesInforList.Add(new OperatorRolesData()
                        {

                            Server = Common.MyIsDBNull(reaccount, "Server"),
                            OperatorID = Common.MyIsDBNull(reaccount, "OperatorID"),
                            OpName = Common.MyIsDBNull(reaccount, "OpName"),
                            Unit = Common.MyIsDBNull(reaccount, "Unit"),
                            Level = Common.MyIsDBNull(reaccount, "Level"),
                            Distinguish = Common.MyIsDBNull(reaccount, "Distinguish"),
                            Trimer = Common.MyIsDBNull(reaccount, "Trimer"),
                            CndidacyJudge_English = Common.MyIsDBNull(reaccount, "CandidacyJudge_English"),
                            AB_English = Common.MyIsDBNull(reaccount, "AB_English"),
                            D_English = Common.MyIsDBNull(reaccount, "D_English"),
                            E_English = Common.MyIsDBNull(reaccount, "E_English"),
                            CandidacyJudge_Japan = Common.MyIsDBNull(reaccount, "CandidacyJudge_Japan"),
                            AB_Japan = Common.MyIsDBNull(reaccount, "AB_Japan"),
                            D_Japan = Common.MyIsDBNull(reaccount, "D_Japan"),
                            E_Japan = Common.MyIsDBNull(reaccount, "E_Japan"),
                            CandidacyJudge_Other = Common.MyIsDBNull(reaccount, "CandidacyJudge_Other"),
                            AB_Other = Common.MyIsDBNull(reaccount, "AB_Other"),
                            D_Other = Common.MyIsDBNull(reaccount, "D_Other"),
                            E_Other = Common.MyIsDBNull(reaccount, "E_Other"),

                            #region--08/13/2010--增加中国语权限 和 英数日语A权限--
                            A_English = Common.MyIsDBNull(reaccount, "A_English"),
                            A_Japan = Common.MyIsDBNull(reaccount, "A_Japan"),
                            A_Chinese = Common.MyIsDBNull(reaccount, "A_Chinese"),
                            AB_Chinese = Common.MyIsDBNull(reaccount, "AB_Chinese"),
                            D_Chinese = Common.MyIsDBNull(reaccount, "D_Chinese"),
                            E_Chinese = Common.MyIsDBNull(reaccount, "E_Chinese"),
                            #endregion

                            #region--12/24/2010-- 增加 F和G 对应权限字段
                            F_English =Common.MyIsDBNull(reaccount ,"F_English"),
                            G_English = Common.MyIsDBNull(reaccount, "G_English"),
                            F_Japan = Common.MyIsDBNull(reaccount, "F_Japan"),
                            G_Japan = Common.MyIsDBNull(reaccount, "G_Japan"),
                            F_Chinese = Common.MyIsDBNull(reaccount, "F_Chinese"),
                            G_Chinese = Common.MyIsDBNull(reaccount, "G_Chinese")
                            #endregion
                        });
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return OperatorRolesInforList;

                #endregion SqlCommand
            }
        }
        #endregion OP权限查询

        #region 数据抽取
        /// <summary>
        /// 基礎データ抽取
        /// </summary>
        /// <param name="wherePar">输入参数</param>
        /// <param name="strOrder">オーダ完了标致</param>
        /// <returns></returns>
        /// <value>11/08/2010 增加</value>
        public static List<DataExtractionData> GetDataExtractionInfor(WhereParameters wherePar, string strOrder) 
        {
            List<DataExtractionData> DataExtractionDataList = new List<DataExtractionData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("EntryBaseQuery", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starDate", wherePar.WhereentryDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@finishDate", wherePar.WhereentryDateTo));

                    cmd.Parameters.Add(new SqlParameter("@OrderKind", SqlDbType.NVarChar));
                    cmd.Parameters["@OrderKind"].Value = strOrder;

                    cmd.CommandTimeout = 380;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        DataExtractionDataList.Add(new DataExtractionData()
                        {
                            
                            EntryDate = Common.MyToFormat(reaccount, "EntryDate", Common.ClassType.DATETIME, "yyyy-MM-dd"),
                            //line
                            MaServer = Common.MyIsDBNull(reaccount, "Line"),
                            //帳票ID
                            MatterID = Common.MyIsDBNull(reaccount, "MatterID"),
                            //帳票名称
                            MatterName = Common.MyIsDBNull(reaccount, "MatterName"),
                            //社員ID
                            EmpID = Common.MyIsDBNull(reaccount, "EmpID"),
                            //社員名
                            OpName = Common.MyIsDBNull(reaccount, "OpName"),
                            //OPID
                            LoginName = Common.MyIsDBNull(reaccount, "LoginName"),
                            //Unit
                            Unit = Common.MyIsDBNull(reaccount, "Unit"),
                            //SV名前
                            SV = Common.MyIsDBNull(reaccount, "SV"),
                            //工程
                            JobClass = Common.MyIsDBNull(reaccount, "jobClass"),
                            //言語種類
                            WordType = Common.MyIsDBNull(reaccount, "WordType"),
                            //印字種類
                            ScriptType = Common.MyIsDBNull(reaccount, "ScriptType"),
                            //子画像数
                            ChildDataCount = Common.MyToFormat(reaccount, "ChildDataCount", Common.ClassType.DOUBLE, "#,##0"),
                            //正解子画像数 
                            OKChildDataCount = Common.MyToFormat(reaccount, "OKChildDataCount", Common.ClassType.DOUBLE, "#,##0"),
                            //ミス子画像数
                            NGChildDataCount = Common.MyToFormat(reaccount, "NGChildDataCount", Common.ClassType.DOUBLE, "#,##0"),
                            //文字数
                            WordCount = Common.MyToFormat(reaccount, "WordCount", Common.ClassType.DOUBLE, "#,##0"),
                            //処理時間(DL,UP含め)
                            PropagationTime = Common.MyToFormat(reaccount, "PropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),
                            //入力時間
                            Duration = Common.MyToFormat(reaccount, "Duration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //出勤時間
                            OnDutyDate = Common.MyToFormat(reaccount, "OnDutyDate", Common.ClassType.DATETIME, "yyyy-MM-dd HH:mm:ss"),
                            //退勤時間
                            OffDutyDate = Common.MyToFormat(reaccount, "OffDutyDate", Common.ClassType.DATETIME, "yyyy-MM-dd HH:mm:ss"),
                             
                        });
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return DataExtractionDataList;

                #endregion SqlCommand
            }
        }

        /// <summary>
        /// オーダ情報 抽取
        /// </summary>
        /// <param name="wherePar">输入参数</param>
        /// <returns></returns>
        /// <value >11/10/2010 add </value>
        public static List<OrderDate> GetOrderInfo(WhereParameters wherePar) 
        {
            List<OrderDate> OrderDateList = new List<OrderDate>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("OrderBaseQuery", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starDate", wherePar.WhereentryDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@finishDate", wherePar.WhereentryDateTo));


                    cmd.CommandTimeout = 380;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        OrderDateList.Add(new OrderDate()
                        {
                            //日付
                            QueryDate = Common.MyToFormat(reaccount, "QueryDate", Common.ClassType.DATETIME, "yyyy-MM-dd"),
                            //line
                            Lines = Common.MyIsDBNull(reaccount, "Lines"),
                            //帳票ID
                            MatterID = Common.MyIsDBNull(reaccount, "MatterID"),
                            //帳票名称
                            MatterName = Common.MyIsDBNull(reaccount, "MatterName"),

                            //枚数
                            DataCount = Common.MyToFormat(reaccount, "DataCount", Common.ClassType.DOUBLE, "#,##0"),
                            //納品文字数
                            WrodCount = Common.MyToFormat(reaccount, "WrodCount", Common.ClassType.DOUBLE, "#,##0"),
                           
                            //仕分入力時間(s)
                            DistinguishDuration = Common.MyToFormat(reaccount, "DistinguishDuration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //仕分処理時間(s)
                            DistinguishPropagationTime = Common.MyToFormat(reaccount, "DistinguishPropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                            //トリマ入力時間(s)
                            TrimerDuration = Common.MyToFormat(reaccount, "TrimerDuration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //トリマ処理時間(s)
                            TrimerPropagationTime = Common.MyToFormat(reaccount, "TrimerPropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                            //α入力時間(s)
                            AlphaDuration = Common.MyToFormat(reaccount, "αDuration",Common.ClassType.DOUBLE, "#,##0.000"),
                            //α処理時間(s)
                            AlphaPropagationTime = Common.MyToFormat(reaccount, "αPropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                            //β入力時間(s)
                            BetaDuration = Common.MyToFormat(reaccount, "βDuration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //β処理時間(s)
                            BetaPropagationTime = Common.MyToFormat(reaccount, "βPropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                            //A录入时间(s)
                            ADuration = Common.MyToFormat(reaccount, "ADuration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //A処理時間(s)
                            APropagationTime = Common.MyToFormat(reaccount, "APropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                            //B录入时间(s)
                            BDuration = Common.MyToFormat(reaccount, "BDuration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //B処理時間(s)
                            BPropagationTime = Common.MyToFormat(reaccount, "BPropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                            //D录入时间(s)
                            DDuration = Common.MyToFormat(reaccount, "DDuration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //D処理時間(s)
                            DPropagationTime = Common.MyToFormat(reaccount, "DPropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                            //E录入时间(s)
                            EDuration = Common.MyToFormat(reaccount, "EDuration", Common.ClassType.DOUBLE, "#,##0.000"),
                            //E処理時間(s)
                            EPropagationTime = Common.MyToFormat(reaccount, "EPropagationTime", Common.ClassType.DOUBLE, "#,##0.000"),

                        });
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return OrderDateList;

                #endregion SqlCommand
            }
        }
        
        #endregion 数据抽取

        #region ParintID查询信息
        /// <summary>
        /// ParintID查询信息 V2.13
        /// </summary>
        /// <param name="wherePar">输入参数</param>
        /// <returns></returns>
        /// <value>06/29/2011 增加</value>
        public static List<ParentIDDate> GetParentIDInfor(WhereParameters wherePar)
        {
            List<ParentIDDate> ParentIDDateDataList = new List<ParentIDDate>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("ReportParentInformationQuery", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    //@starDate = NULL,
                    //@FinIShDate = NULL,
                    //@MatterID = WhereOpID
                    //@Lines = WhereServer
                    //@UserOrder = WhereUnit

                    cmd.Parameters.Add(new SqlParameter("@starDate", wherePar.WhereentryDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@FinIShDate", wherePar.WhereentryDateTo));
                    cmd.Parameters.Add(new SqlParameter("@MatterID", wherePar.WhereOpID));
                    cmd.Parameters.Add(new SqlParameter("@Lines", wherePar.WhereServer));
                    cmd.Parameters.Add(new SqlParameter("@UserOrder", wherePar.WhereUnit));

                    cmd.CommandTimeout = 380;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        ParentIDDateDataList.Add(new ParentIDDate()
                        {
                            // 录入日期
                            DeliverDate = Common.MyToFormat(reaccount, "DeliverDate", Common.ClassType.DATETIME, "yyyy-MM-dd"),

                            OrderID = Common.MyIsDBNull(reaccount, "OrderID"),
                            
                            ParentID = Common.MyIsDBNull(reaccount, "ParentID"),

                            MatterID = Common.MyIsDBNull(reaccount, "MatterID"),

                            MatterName = Common.MyIsDBNull(reaccount, "MatterName"),

                            Lines = Common.MyIsDBNull(reaccount, "Lines"),

                            UserOrder = Common.MyIsDBNull(reaccount, "UserOrder"),

                            DeliverDateTime = Common.MyIsDBNull(reaccount, "DeliverDateTime")
                           

                        });
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return ParentIDDateDataList;

                #endregion SqlCommand
            }
        }
        #endregion ParintID查询信息
    }
}
